using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.StaticFiles;
using Microsoft.Extensions.Logging;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OfficeOpenXml;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using Xxx;
using Xxx.AuxiliaryEntity.Db;
using Xxx.BI.Parameter.Dto;
using Xxx.BI.Parameter.Model.Entities;
using Xxx.iW;

namespace WebAPI.Controllers
{
    /// <summary>
    /// ParameterController
    /// </summary>
    /// <remarks>
    /// <para/>Author   :  XingYaoGuang
    /// <para/>Date     :  2021-06-23 16:31
    /// </remarks>
    [ApiController]
    [Route("api/[controller]/[action]")]
    public class ParameterController : ControllerBase
    {
        //建立一个数据仓库访问对象 Repository
        private readonly IRepository repository = new Repository<SqlConnection>(""); // SqlServer 示例中使用的是SqlServer
        //日志
        private readonly ILogger<ParameterController> _logger;
        /// <summary>
        /// _webHostEnvironment
        /// </summary>
        public readonly IWebHostEnvironment _webHostEnvironment;
        /// <summary>
        /// ParameterController初始化
        /// </summary>
        /// <param name="webHostEnvironment">webHostEnvironment服务上下文</param>
        /// <param name="logger">日志</param>
        public ParameterController(IWebHostEnvironment webHostEnvironment, ILogger<ParameterController> logger)
        {
            _webHostEnvironment = webHostEnvironment;
            _logger = logger;
        }

        /// <summary>
        /// 查找全部数据
        /// </summary>
        /// <remarks>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// </remarks>
        /// <returns>全部数据</returns>
        [HttpGet]
        public IEnumerable<Sys_Parameter> Query(string code,string name_Cn, string name_En,
            string parameter_Value,string description)
        {
            //字段对应条件列表
            IList<DbColumn> getWhere = new List<DbColumn>();
            if (!string.IsNullOrEmpty(code)) {
                //Sys_Parameter.F可以点出数据库对应的字段
                getWhere.Add(Sys_Parameter.F.CODE, code);
            }
            if (!string.IsNullOrEmpty(name_Cn))
            {
                //Sys_Parameter.F可以点出数据库对应的字段
                getWhere.Add(Sys_Parameter.F.NAME_CN, name_Cn);
            }
            if (!string.IsNullOrEmpty(name_En))
            {
                //Sys_Parameter.F可以点出数据库对应的字段
                getWhere.Add(Sys_Parameter.F.NAME_EN, name_En);
            }
            if (!string.IsNullOrEmpty(parameter_Value))
            {
                //Sys_Parameter.F可以点出数据库对应的字段
                getWhere.Add(Sys_Parameter.F.PARAMETER_VALUE, parameter_Value);
            }
            if (!string.IsNullOrEmpty(description))
            {
                //Sys_Parameter.F可以点出数据库对应的字段
                getWhere.Add(Sys_Parameter.F.DESCRIPTION, description);
            }
            ////多数据查询 返回迭代器类型
            IEnumerable<Sys_Parameter> ResultList = repository.Get<Sys_Parameter>(where: getWhere, tableName: Sys_Parameter.TABLE_NAME);
            return ResultList;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <remarks>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// </remarks>
        /// <param name="code">代码</param>
        /// <param name="name_Cn">中文</param>
        /// <param name="name_En">英文</param>
        /// <param name="parameter_Value">参数</param>
        /// <param name="description">描述</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="pageIndex">索引页</param>
        /// <returns>分页查询数据</returns>
        [HttpGet]
        public ResultMessage PagerQuery(string code, string name_Cn, string name_En,
            string parameter_Value, string description, int pageSize,int pageIndex)
        {
            string result = string.Empty;
            //分页查询数据传输对象
            Sys_ParameterCriteriaDto dto = new Sys_ParameterCriteriaDto();
            
            
            
            dto.PageSize = pageSize;//每页X条
            dto.PageIndex = pageIndex;//第X页
            if (!string.IsNullOrEmpty(code))
            {
                dto.Code = code;
            }
            if (!string.IsNullOrEmpty(name_Cn))
            {
                dto.Name_Cn = name_Cn;
            }
            if (!string.IsNullOrEmpty(name_En))
            {
                dto.Name_En = name_En;
            }
            if (!string.IsNullOrEmpty(parameter_Value))
            {
                dto.Parameter_Value = parameter_Value;
            }
            if (!string.IsNullOrEmpty(description))
            {
                dto.Description = description;
            }
            //分页查询 排序会根据 数据传输对象 上的属性[OrderByDesc(FixedValue.ID)]来定义 返回迭代器类型
            IEnumerable<Sys_Parameter> ResultPagerList = repository.GetPager<Sys_Parameter, Sys_ParameterCriteriaDto>(criteria: dto, buffered: true, commandTimeout: 120, CommandType.Text, Sys_Parameter.TABLE_NAME, Sys_Parameter.TABLE_ALIAS);
            ResultMessage re = new ResultMessage();
            re.TotalCount = dto.TotalCount;
            re.ResultData = ResultPagerList;
            return re;
        }
        /// <summary>
        ///  自定义前台返回数据格式
        /// </summary>
        ///  <remarks>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// </remarks>
        public class ResultMessage
        { 
            /// <summary>
            /// 总数
            /// </summary>
            public long TotalCount { get; set; }
            /// <summary>
            /// ResultData数据
            /// </summary>
            public IEnumerable<Sys_Parameter> ResultData { get; set; }
        }

        // GET: api/GetParameter/5
        /// <summary>
        /// 查找指定数据
        /// </summary>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// <param name="id">数据id</param>
        /// <returns>指定数据</returns>
        [HttpGet]
        public Sys_Parameter GetParameter(long id)
        {
            //字段对应条件列表
            IList<DbColumn> getWhere = new List<DbColumn>();
            //Sys_Parameter.F可以点出数据库对应的字段
            getWhere.Add(Sys_Parameter.F.ID, id);
            //单数据查询 返回对象实体类型
            Sys_Parameter ResultFirstDefault = repository.GetFirstOrDefault<Sys_Parameter>(where: getWhere, db: null, tran: null, commandTimeout: 120, commandType: CommandType.Text, tableName: Sys_Parameter.TABLE_NAME, tableAlias: Sys_Parameter.TABLE_ALIAS);
            return ResultFirstDefault;
        }

        // PUT: api/UpdateParameter/5
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// <param name="id">旧数据id</param>
        /// <param name="code">更新代码</param>
        /// <param name="name_Cn">更新中文</param>
        /// <param name="name_En">更新英文</param>
        /// <param name="parameter_Value">更新参数值</param>
        /// <param name="description">描述</param>
        /// <returns>更新结果</returns>
        [HttpPut]
        public bool UpdateParameter(long id, string code, string name_Cn, string name_En,
            string parameter_Value, string description)
        {
            Sys_Parameter entity = GetParameter(id);
            //字段对应条件列表
            IList<DbColumn> getWhere = new List<DbColumn>();
            getWhere.Add(Sys_Parameter.F.ID, id);
            if (!string.IsNullOrEmpty(code))
            {
                entity.Code = code;
            }
            if (!string.IsNullOrEmpty(name_Cn))
            {
                entity.Name_Cn = name_Cn;
            }
            if (!string.IsNullOrEmpty(name_En))
            {
                entity.Name_En = name_En;
            }
            if (!string.IsNullOrEmpty(parameter_Value))
            {
                entity.Parameter_Value = parameter_Value;
            }
            if (!string.IsNullOrEmpty(description))
            {
                entity.Description = description;
            }
            //根据实体来更新数据库数据 返回结果为更新成功数
            int updateResult1 = repository.Update<Sys_Parameter>(entity: entity, whereField: getWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text);

            return updateResult1>0;
        }

        // POST: api/AddParameter
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// <param name="code">代码</param>
        /// <param name="name_Cn">中文</param>
        /// <param name="name_En">英文</param>
        /// <param name="parameter_Value">参数值</param>
        /// <param name="description">描述</param>
        /// <returns>添加结果</returns>
        [HttpPost]
        public bool AddParameter(string code, string name_Cn, string name_En,
            string parameter_Value, string description)
        {
            Sys_Parameter entity = new Sys_Parameter();
            if (!string.IsNullOrEmpty(code))
            {
                entity.Code = code;
            }
            if (!string.IsNullOrEmpty(name_Cn))
            {
                entity.Name_Cn = name_Cn;
            }
            if (!string.IsNullOrEmpty(name_En))
            {
                entity.Name_En = name_En;
            }
            if (!string.IsNullOrEmpty(parameter_Value))
            {
                entity.Parameter_Value = parameter_Value;
            }
            if (!string.IsNullOrEmpty(description))
            {
                entity.Description = description;
            }
            // 返回值为long 因为返回新增的主键ID是BIGINT类型
            long addResult = repository.Add(entity, db: null, tran: null, tableName: Sys_Parameter.TABLE_NAME, returnIdentity: true, commandTimeout: 120, commandType: CommandType.Text);
            return addResult > 0;
        }

        // DELETE: api/DeleteParameter/5
        /// <summary>
        /// 删除指定id
        /// </summary>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// <param name="id">数据id</param>
        /// <returns>是否删除成功</returns>
        [HttpDelete]
        public bool DeleteParameter(long id)
        {
            //long id = Convert.ToInt64(deleteId);
            //字段对应条件列表
            IList<DbColumn> deleteWhere = new List<DbColumn>();
            deleteWhere.Add(Sys_Parameter.F.ID, id);
            //根据字段对应条件列表来删除数据库数据 返回结果为删除成功数
            int deleteResult = repository.Delete<Sys_Parameter>(where: deleteWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text);
            return deleteResult > 0;
        }


        // Exist: api/ExistParameter/5
        /// <summary>
        /// 当前代码是否存在
        /// </summary>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// <param name="code">代码</param>
        /// <returns>是否存在</returns>
        [HttpGet]
        public bool ExistParameter(string code)
        {
            //字段对应条件列表
            IList<DbColumn> getWhere = new List<DbColumn>();
            getWhere.Add(Sys_Parameter.F.CODE, code);
            bool existesult = repository.Exist<Sys_Parameter>(where: getWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text, tableName: Sys_Parameter.TABLE_NAME, tableAlias: Sys_Parameter.TABLE_ALIAS);
            return existesult;
        }

        // Exist: api/ExistUpdateParameter/5
        /// <summary>
        /// 编辑判断当前代码是否存在（除了本身）
        /// </summary>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// <param name="id">旧id</param>
        /// <param name="code">更新代码</param>
        /// <returns>是否存在</returns>
        [HttpGet]
        public bool ExistUpdateParameter(long id,string code)
        {
            bool existesult = false;
            //字段对应条件列表
            IList<DbColumn> getWhere = new List<DbColumn>();
            //字段对应条件列表
            if (!string.IsNullOrEmpty(code))
            {
                //Sys_Parameter.F可以点出数据库对应的字段
                getWhere.Add(Sys_Parameter.F.CODE, code);
            }
           
            ////多数据查询 返回迭代器类型
            IEnumerable<Sys_Parameter> ResultList = repository.Get<Sys_Parameter>(where: getWhere, tableName: Sys_Parameter.TABLE_NAME);
            if (ResultList.ToListOrDefault<Sys_Parameter>().Count == 1 && ResultList.ToListOrDefault<Sys_Parameter>()[0].Id != id)
            {
                existesult = true;
            }
            return existesult;
        }

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-21 14:43
        /// <returns>Blob文件流</returns>
        [HttpGet]
        public FileContentResult Export()
        {            
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("测试NPOI");
            sheet.DefaultColumnWidth = 20;
            sheet.ForceFormulaRecalculation = true;
            //标题字体
            var headFont = workbook.CreateFont();
            headFont.FontName = "Aria"; //设置字体
            headFont.FontHeightInPoints = 11;  //设置字体大小
            //设置加粗
            headFont.IsBold = true;
            //标题列样式
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;//设置列头水平居中
            headStyle.VerticalAlignment = VerticalAlignment.Center;  //设置列头垂直居中
            //设置边框线
            headStyle.BorderTop = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderBottom = BorderStyle.Thin;
            //这里可以设置列头边框颜色
            headStyle.BottomBorderColor = HSSFColor.Blue.Index;
            headStyle.TopBorderColor = HSSFColor.Blue.Index;
            headStyle.LeftBorderColor = HSSFColor.Blue.Index;
            headStyle.RightBorderColor = HSSFColor.Blue.Index;
            //这里可以设置列头背景颜色
            headStyle.FillForegroundColor = HSSFColor.LightTurquoise.Index;
            headStyle.FillPattern = FillPattern.SolidForeground;
            headStyle.SetFont(headFont);

            var rowIndex = 0;
            var row = sheet.CreateRow(rowIndex);
            var cell = row.CreateCell(0);
            cell.SetCellValue("代码");
            cell.CellStyle = headStyle;

            cell = row.CreateCell(1);
            cell.SetCellValue("中文名称");
            cell.CellStyle = headStyle;

            cell = row.CreateCell(2);
            cell.SetCellValue("英文名称");
            cell.CellStyle = headStyle;

            cell = row.CreateCell(3);
            cell.SetCellValue("参数值");
            cell.CellStyle = headStyle;

            cell = row.CreateCell(4);
            cell.SetCellValue("描述");
            cell.CellStyle = headStyle;

            //单元格边框
            var cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;

            //字段对应条件列表
            IList<DbColumn> getWhere = new List<DbColumn>();
            ////多数据查询 返回迭代器类型
            List<Sys_Parameter> ResultList = (List<Sys_Parameter>)repository.Get<Sys_Parameter>(where: getWhere, tableName: Sys_Parameter.TABLE_NAME);

            for (var i = 0; i < ResultList.Count; i++)
            {
                row = sheet.CreateRow(i + 1);
                cell = row.CreateCell(0);
                cell.SetCellValue(ResultList[i].Code);
                cell.CellStyle = cellStyle;

                cell = row.CreateCell(1);
                cell.SetCellValue(ResultList[i].Name_Cn);
                cell.CellStyle = cellStyle;

                cell = row.CreateCell(2);
                cell.SetCellValue(ResultList[i].Name_En);
                cell.CellStyle = cellStyle;

                cell = row.CreateCell(3);
                cell.SetCellValue(ResultList[i].Parameter_Value);
                cell.CellStyle = cellStyle;

                cell = row.CreateCell(4);
                cell.SetCellValue(ResultList[i].Description);
                cell.CellStyle = cellStyle;
            }

            ////公式计算
            //row = sheet.CreateRow(7);
            //cell = row.CreateCell(3);
            //cell.SetCellValue(100);

            //cell = row.CreateCell(4);
            //cell.SetCellValue(200);

            //cell = row.CreateCell(5);
            //cell.CellFormula = "D8+E8";

            ////获取公式值  如果要遍历找出是否公式单元格   CellType判断
            //var e = new HSSFFormulaEvaluator(workbook);
            //cell = e.EvaluateInCell(cell);

            //var cell1 = row.CreateCell(6);
            //cell1.SetCellValue(cell.NumericCellValue);

            //获取存储静态资源的wwwroot所在的根目录
            //string ip = System.Net.Dns.GetHostEntry(System.Net.Dns.GetHostName()).AddressList.FirstOrDefault(address => address.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork)?.ToString();

            //获取项目的根目录（）
            //string rootpath = _webHostEnvironment.ContentRootPath;

            //Excel的缓存路径及名称
            var fileDir = @"D:\导出缓存";
            //判断文件夹是否存在，不存在就创建
            if (!Directory.Exists(fileDir))
            {
                Directory.CreateDirectory(fileDir);
            }
            string excelPath = fileDir + @"\导出AA1.xls";
            FileStream fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (!workbook.IsWriteProtected)
            {
                workbook.Write(fileStream);
            }
            fileStream.Close();

            //关键语句 返回Blob文件流
            byte[] fileBytes = System.IO.File.ReadAllBytes(excelPath);
            string fileName = "导出AA1.xls";
            return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);

        }

        /// <summary>
        ///  数据导入
        /// </summary>
        /// <remarks>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-23 16:44
        /// </remarks>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        public string ImportParameter(IFormFile file)
        {
            ////导入缓存文件备份服务器，然后在读取
            //if (file != null)
            //{
            //    var fileDir = "D:\\导入缓存";
            //    if (!Directory.Exists(fileDir))
            //    {
            //        Directory.CreateDirectory(fileDir);
            //    }
            //    //文件名称
            //    string projectFileName = file.FileName;

            //    //上传的文件的路径
            //    string filePath = fileDir + $@"\{projectFileName}";
            //    using (FileStream fs = System.IO.File.Create(filePath))
            //    {
            //        file.CopyTo(fs);
            //        fs.Flush();
            //    }
            //}

            //var files = Request.Form.Files;            
            string ReturnValue = string.Empty;
            //定义一个bool类型的变量用来做验证
            bool flag = true;
            try
            {
                
                string fileExt = Path.GetExtension(file.FileName).ToLower();                
                _logger.LogInformation(fileExt);
                //定义一个DataTable集合一会儿将数据存储进来,全部一次丢到数据库中保存
                DataTable parameterTable = new DataTable();
                // 添加表头                
                parameterTable.Columns.Add("Unique_No");
                parameterTable.Columns.Add("Code");
                parameterTable.Columns.Add("Name_Cn");
                parameterTable.Columns.Add("Name_En");
                parameterTable.Columns.Add("Parameter_Value");
                parameterTable.Columns.Add("Description");
                MemoryStream ms = new MemoryStream();
                file.CopyTo(ms);
                ms.Seek(0, SeekOrigin.Begin);
                IWorkbook book;
                if (fileExt == ".xlsx")
                {
                    book = new XSSFWorkbook(ms);
                }
                else if (fileExt == ".xls")
                {
                    book = new HSSFWorkbook(ms);
                }
                else
                {
                    book = null;
                }
                ISheet sheet = book.GetSheetAt(0);

                int CountRow = sheet.LastRowNum + 1;//获取总行数
                _logger.LogInformation(CountRow.ToString());
                if (CountRow - 1 == 0)
                {
                    return "Excel列表数据项为空!";

                }
                #region 循环验证
                for (int i = 1; i < CountRow; i++)
                {
                    //获取第i行的数据
                    var row = sheet.GetRow(i);
                    if (row != null)
                    {
                        //循环的验证单元格中的数据
                        for (int j = 0; j < 1; j++)
                        {
                            if (row.GetCell(j) == null || row.GetCell(j).ToString().Trim().Length == 0)
                            {
                                flag = false;
                                ReturnValue += $"第{i + 1}行,第{j + 1}列数据不能为空。";
                            }
                        }
                    }
                }
                #endregion
                if (flag)
                {
                    for (int i = 1; i < CountRow; i++)
                    {
                        //excel对象赋值到Datatable:parameterTable
                        DataRow newDataRow = parameterTable.NewRow();                       
                        var row = sheet.GetRow(i);
                        newDataRow["Unique_No"] = Guid.NewGuid().ToString();
                        if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0)
                        {
                            newDataRow["Code"] = row.GetCell(0) == null ? "" : row.GetCell(0).ToString();
                        }
                        if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0)
                        {
                            newDataRow["Name_Cn"] = row.GetCell(1) == null ? "" : row.GetCell(1).ToString();
                        }
                        if (row.GetCell(2) != null && row.GetCell(2).ToString().Trim().Length > 0)
                        {
                            newDataRow["Name_En"] = row.GetCell(2) == null ? "" : row.GetCell(2).ToString();
                        }
                        if (row.GetCell(3) != null && row.GetCell(3).ToString().Trim().Length > 0)
                        {
                            newDataRow["Parameter_Value"] = row.GetCell(3) == null ? "" : row.GetCell(3).ToString();
                        }
                        if (row.GetCell(4) != null && row.GetCell(4).ToString().Trim().Length > 0)
                        {
                            newDataRow["Description"] = row.GetCell(4) == null ? "" : row.GetCell(4).ToString();
                        }
                        parameterTable.Rows.Add(newDataRow);
                    }

                    //使用仓储对象创建一个连接
                    using IDbConnection con = repository.CreateConnection();
                    //使用连接创建一个事务
                    using IDbTransaction tran = con.BeginTransaction();
                    //批量导入之前，清空原有数据       
                    IList<DbColumn> deleteWhere = new List<DbColumn>();
                    //根据字段对应条件列表来删除数据库数据 返回结果为删除成功数
                    int deleteResult = repository.Delete<Sys_Parameter>(where: deleteWhere, db: con, tran: tran, commandTimeout: 120, CommandType.Text);
                    
                    //批量导入
                    bool batchInsert=BatchImport(parameterTable, con, tran);
                    if (batchInsert)
                    {
                        ReturnValue = $"数据导入成功,共导入{CountRow - 1}条数据。";
                    }
                    else {
                        ReturnValue = $"导入失败";
                    }
                }

                if (!flag)
                {
                    ReturnValue = "数据存在问题！" + ReturnValue;
                }
            }
            catch (Exception e)
            {
                return "服务器异常:"+e.Message;
            }

            return ReturnValue;
        }

        /// <summary>
        /// 使用SqlBulkCopy批量插入
        /// </summary>
        /// <remarks>
        /// <para/>Author   :  XingYaoGuang
        /// <para/>Date     :  2021-06-23 16:44
        /// </remarks>        
        /// <param name="parameterTable">批量插入数据</param>
        /// <param name="conn">数据库连接</param>
        /// <param name="tran">事务</param>
        /// <returns></returns>
        private bool BatchImport(DataTable parameterTable,IDbConnection conn,IDbTransaction tran)
        {
            //标记是否导入成功
            bool isSucc = false;
            try
            {                             
                //在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务
                SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection)conn, SqlBulkCopyOptions.CheckConstraints, (SqlTransaction)tran);
                bulkCopy.DestinationTableName = Sys_Parameter.TABLE_NAME;//代表要插入数据的表名
                foreach (DataColumn dc in parameterTable.Columns)  //传入上述table
                {
                    bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);//将table中的列与数据库表这的列一一对应
                }
                try
                {
                    bulkCopy.WriteToServer(parameterTable);
                    tran.Commit();
                    isSucc = true;
                }
                catch (Exception)
                {
                    tran.Rollback();
                    throw;
                }
                finally
                {
                    bulkCopy.Close();
                    conn.Close();
                }
                return isSucc;
            }
            catch
            {
                return isSucc;
            }
        }
    }
}
